package com.xyw.Dao.impl;
import java.sql.*;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.xyw.Dao.UserDao;
import com.xyw.entity.User;
import com.xyw.util.JDBCUtil;


public class UserDaoImpl implements UserDao {
	JDBCUtil util=JDBCUtil.getInit();
	private Connection connection = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;
	//登录
	public User login(String name, String pwd) {
		User u=null;
		CachedRowSet rs=util.query("select * from xyw_user where User_name=? and User_password=?",name,pwd);
		try {
			if(rs.next()) {
				u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		
		return u;
	}

	//查询全部
	public List<User> queryall(){
		List<User> list=new ArrayList<User>();
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user order by User_id";
		CachedRowSet rs=util.query(sql);
		try {
			while(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				list.add(u);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
		
	};
	
	//通过ID查询
	public User queryByID(int id) {
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_id=?";
		CachedRowSet rs=util.query(sql,id);
		try {
			if(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				return u;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}

	//根据用户名查询
	public User queryByName(String name) {
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_name=?";
		CachedRowSet rs=util.query(sql,name);
		try {
			if(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				return u;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}

	//根据邮箱查询
	public User queryByEmail(String email) {
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_email=?";
		CachedRowSet rs=util.query(sql,email);
		try {
			if(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				return u;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}

	//根据年龄查询
	public List<User> queryByAge(int age) {
		List<User> list=new ArrayList<User>();
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_age=?";
		CachedRowSet rs=util.query(sql,age);
		try {
			while(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				list.add(u);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}

	//根据电话查询
	public User queryByPhone(String phone) {
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_telephone_number=?";
		CachedRowSet rs=util.query(sql,phone);
		try {
			if(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				return u;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}

	//根据用户昵称查询
	public List<User> queryByNickname(String nickname) {
		List<User> list=new ArrayList<User>();
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_nickname=?";
		CachedRowSet rs=util.query(sql,nickname);
		try {
			while(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				list.add(u);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}

	@Override
	public List<User> queryByJurisdiction(String jurisdiction) {
		List<User> list=new ArrayList<User>();
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_jurisdiction=?";
		CachedRowSet rs=util.query(sql,jurisdiction);
		try {
			while(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				list.add(u);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
		
	}

	//根据ID删除
	public boolean deleteByID(int id) {
		return util.update("delete from xyw_user where User_id=?", id);
	}

	//修改用户信息
	public User update(User user) {
		String sql="update xyw_user set User_name=?,User_password=?,User_email=?,User_profile_photo=?,User_birthday=?,User_age=?,User_telephone_number=?,User_nickname=? where User_id=?";
		boolean flag= util.update(sql, user.getUserName(),user.getUserPassword(),user.getUserEmail(),user.getUserProfilePhoto(),user.getUserBirthday(),user.getUserAge(),user.getUserTelephoneNumber(),user.getUserNickname(),user.getUserId());
	    if(flag) {
	    	return login(user.getUserName(), user.getUserPassword());
	    }else {
	    	return null;
	    }
	}

	//添加用户
	public User add(User user) {
		String sql="insert into xyw_user(User_name,User_password,User_email,User_profile_photo,User_birthday,User_age,User_telephone_number,User_nickname,User_jurisdiction) values (?,?,?,?,?,?,?,?,?)";
		boolean flag=util.update(sql, user.getUserName(),user.getUserPassword(),user.getUserEmail(),user.getUserProfilePhoto(),user.getUserBirthday(),user.getUserAge(),user.getUserTelephoneNumber(),user.getUserNickname(),user.getUserJurisdiction());
		if(flag) {
	    	return login(user.getUserName(), user.getUserPassword());
	    }else {
	    	return null;
	    }
	}

	@Override
	public User getShang(int id) {
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_id<? limit 1";
		CachedRowSet rs=util.query(sql,id);
		try {
			if(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				return u;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}

	@Override
	public User getXia(int id) {
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user where User_id>? limit 1";
		CachedRowSet rs=util.query(sql,id);
		try {
			if(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				return u;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return null;
	}

	@Override
	public int getCount() {
		String sql="select count(User_id) mycount from xyw_user";
		CachedRowSet rs=util.query(sql);
		try {
			if(rs.next()) {
				return rs.getInt("mycount");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return -1;
	}

	@Override
	public List<User> queryByPage(int page, int size) {
		List<User> list=new ArrayList<User>();
		String sql="select *,DATE_FORMAT(User_registration_time,'%Y-%m-%d %H:%i:%s') mytime from xyw_user order by User_id limit "+(page-1)*size+" , "+size;
		CachedRowSet rs=util.query(sql);
		try {
			while(rs.next()) {
				User u=new User();
				u.setUserId(rs.getInt("User_id"));
				u.setUserName(rs.getString("User_name"));
				u.setUserPassword(rs.getString("User_password"));
				u.setUserEmail(rs.getString("User_email"));
				u.setUserProfilePhoto(rs.getString("User_profile_photo"));
				u.setUserRegistrationTime(rs.getTimestamp("User_registration_time"));
				u.setUserBirthday(rs.getDate("User_birthday"));
				u.setUserAge(rs.getInt("User_age"));
				u.setUserTelephoneNumber(rs.getString("User_telephone_number"));
				u.setUserNickname(rs.getString("User_nickname"));
				u.setUserJurisdiction(rs.getInt("User_jurisdiction"));
				list.add(u);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}
		return list;
	}

	@Override
	public Boolean checkName(String name) {
		String sql="select * from xyw_user where User_name=?";
		CachedRowSet rs=util.query(sql,name);
		try {
			if(rs.next()) {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}		
		return true;
	}

	@Override
	public Boolean checkEmail(String email) {
		String sql="select * from xyw_user where User_email=?";
		CachedRowSet rs=util.query(sql,email);
		try {
			if(rs.next()) {
                return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}		
		return true;
	}

	@Override
	public Boolean checkPhone(String phone) {
		String sql="select * from xyw_user where User_telephone_number=?";
		CachedRowSet rs=util.query(sql,phone);
		try {
			if(rs.next()) {
				return false;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			util.closeCacheRowSet(rs);
		}		
		return true;
	}


	@Override
	public Boolean updatePwd(String name) {
		String sql="update xyw_user set User_password=123 where User_name=?";
	    return util.update(sql, name);
	    		
	}




}
